<?php
include_once './baseSQL.php';

class CouSql extends BaseSql
{

    function insert($cou)
    {
        
        try {
            $dbh = new PDO($this -> dsn, $this -> user, $this -> pass); //初始化一个PDO对象

            $sql = "SELECT * FROM zyh_course WHERE cou_num like '" . $cou['num'] . "'";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                $sql = "INSERT INTO zyh_course (`cou_id`, `cou_num`, `cou_name`, `cou_year`, `cou_term`, `cou_credits`, `cou_time`, `cou_address`, `cou_college`, `cou_cnum`) VALUE(null,'" . $cou['num'] . "','" .$cou['name'] . "','" . $cou['year'] . "'," . $cou['term'] . "," . $cou['credits'] . ",'" . $cou['time'] . "','" . $cou['address'] . "','" . $cou['college'] . "','" . $cou['cnum'] . "')";
                $dbh->exec($sql);
            }

            
            $dbh=null;
            return true;

        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }

    }
    function compareTeacher($data)
    {
        
        try {
            $dbh = new PDO($this -> dsn, $this -> user, $this -> pass); //初始化一个PDO对象

            $sql = "SELECT * FROM zyh_tch_cou WHERE tch_id like '" . $data['teahcerId'] . "' AND cou_id like (SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . ")";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                $sql = "INSERT INTO zyh_tch_cou (`tch_id`, `cou_id`) VALUE('" . $data['teahcerId'] . "',(SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . "))";
                $res = $dbh->exec($sql);
                
            }
            else{
                $res = -1;
            }

            
            $dbh=null;
            return $res;

        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }

    }
    function compareStu($data)
    {
        
        try {
            $dbh = new PDO($this -> dsn, $this -> user, $this -> pass); //初始化一个PDO对象

            $sql = "SELECT * FROM zyh_cou_stu WHERE stu_id like '" . $data['studentId'] . "' AND cou_id like (SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . ")";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                $sql = "INSERT INTO zyh_cou_stu (`stu_id`, `cou_id`) VALUE('" . $data['studentId'] . "',(SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . "))";
                $res = $dbh->exec($sql);
            }
            else{
                $res = -1;
            }

            
            $dbh=null;
            return $res;

        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }

    }

    function compareStu2($data)
    {
        
        try {
            $dbh = new PDO($this -> dsn, $this -> user, $this -> pass); //初始化一个PDO对象

            // 查找学生是否存在
            $sql = "SELECT * FROM zyh_student WHERE stu_id like '" . $data['studentId'] . "'";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                return -1;
            }
            //查找课程是否存在
            $sql = "SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . "";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                return -2;
            }
            // 学生是否已经选课
            $sql = "SELECT * FROM zyh_cou_stu WHERE stu_id like '" . $data['studentId'] . "' AND cou_id like (SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . ")";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt -> rowCount() == 0){
                $sql = "INSERT INTO zyh_cou_stu (`stu_id`, `cou_id`) VALUE('" . $data['studentId'] . "',(SELECT cou_id FROM zyh_course WHERE cou_num like '" . $data['courseId'] . "' AND cou_year like '" . $data['courseYaer'] . "' AND cou_term = " . $data['courseTerm'] . "))";
                $res = $dbh->exec($sql);
            }
            else{
                $res = -3;
            }

            
            $dbh=null;
            return $res;

        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }

    }
    

    function add($arr)
    {

        try {
            $dbh = new PDO($this->dsn, $this->user, $this->pass); //初始化一个PDO对象

            $cou_id = array_key_exists('cou_id', $arr) ? $arr['cou_id'] : '';
            $cou_num = array_key_exists('cou_num', $arr) ? $arr['cou_num'] : '';
            $cou_name = array_key_exists('cou_name', $arr) ? $arr['cou_name'] : '';
            $cou_credits = array_key_exists('cou_credits', $arr) ? $arr['cou_credits'] : 1;
            $cou_college = array_key_exists('cou_college', $arr) ? $arr['cou_college'] : '';
            $cou_term = array_key_exists('cou_term', $arr) ? $arr['cou_term'] : 1;
            $cou_year = array_key_exists('cou_year', $arr) ? $arr['cou_year'] : '';

            if (array_key_exists('isAdd', $arr) && $arr['isAdd']) {                 //添加一条记录
                $sql = "SELECT * FROM zyh_course WHERE cou_id like  " . $cou_id . "";
                $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
                $isExsit = $stmt->rowCount();         //查询是否已存在
                if ($isExsit == 0) {
                    $sql = "INSERT INTO zyh_course (`cou_id`, `cou_num`, `cou_name`, `cou_year`, `cou_term`, `cou_credits`, `cou_time`, `cou_address`, `cou_college`, `cou_cnum`) VALUE(null,'" . $cou_num . "','" . $cou_name . "','" . $cou_year . "'," . $cou_term . "," . $cou_credits . ",'','','" . $cou_college . "','')";
                    $count = $dbh->exec($sql);
                    if ($count > 0) {
                        $result = [
                            'code' => '0',
                            'msg' => '插入信息成功',
                        ];
                    } else {
                        $result = [
                            'code' => '1',
                            'msg' => '插入信息失败',
                        ];
                    }
                } else {
                    $result = [
                        'code' => '1',
                        'msg' => '课程编号已存在',
                    ];
                }
            } else {
                $sql = "UPDATE zyh_course 
                SET `cou_name` = '" . $cou_name . "', `cou_num` = '" . $cou_num . "', `cou_credits` = " . $cou_credits . ", `cou_college` = '" . $cou_college . "', `cou_term` = " . $cou_term . ", `cou_year` = '" . $cou_year . "' 
                WHERE cou_id = " . $cou_id . "";
                $count = $dbh->exec($sql);
                if ($count == 0) {
                    $result = [
                        'code' => '1',
                        'msg' => '修改信息失败',
                    ];
                } else {
                    $result = [
                        'code' => '0',
                        'msg' => '修改信息成功',
                    ];
                }
            }
            $dbh = null;

            //返回json
            echo json_encode($result);
        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }
    }


    
    function crash($cou_id)
    {
        if (!$cou_id){
            $result = [
                'code' => '4',
                'msg' => '课程不应为空',
            ];
            echo json_encode($result);
            return;

        }

        try {
            $dbh = new PDO($this->dsn, $this->user, $this->pass); //初始化一个PDO对象

            $sql = "SELECT * FROM zyh_course WHERE cou_id like " . $cou_id . "";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt->rowCount() > 0){
                $sql = "delete from zyh_cou_stu where cou_id = " . $cou_id . "";
                $count = $dbh->exec($sql);  //删除学生选课记录
                $sql = "delete from zyh_tch_cou where cou_id = " . $cou_id . "";
                $count += $dbh->exec($sql);  //删除教师任课记录

                $sql = "delete from zyh_course where cou_id = " . $cou_id . "";
                $count += $dbh->exec($sql); //删除课程
                if ($count > 0) {
                    $result = [
                        'code' => '0',
                        'msg' => '删除了' . $count . '条记录',
                    ];
                } else {
                    $result = [
                        'code' => '1',
                        'msg' => '删除了' . $count . '条记录',
                    ];
                }
            }
            else{
                $result = [
                    'code' => '2',
                    'msg' => '学生信息不存在！',
                ];

            }

            $dbh = null;

            //返回json
            echo json_encode($result);
        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }
    }

    function crashWithStu($cou_id, $stu_id)
    {
        if (!$cou_id){
            $result = [
                'code' => '2',
                'msg' => '课程不应为空',
            ];
            echo json_encode($result);
            $dbh = null;
            return;

        }
        if (!$stu_id){
            $result = [
                'code' => '3',
                'msg' => '学生不应为空',
            ];
            echo json_encode($result);
            $dbh = null;
            return;

        }

        try {
            $dbh = new PDO($this->dsn, $this->user, $this->pass); //初始化一个PDO对象

            $sql = "delete FROM zyh_cou_stu WHERE stu_id like '" . $stu_id . "' AND cou_id like " . $cou_id . "";
            $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
            if ($stmt->rowCount() > 0){
                $result = [
                    'code' => '0',
                    'msg' => '删除选课记录成功',
                ];
            }
            else{
                $result = [
                    'code' => '1',
                    'msg' => '删除选课记录失败',
                ];

            }

            $dbh = null;

            //返回json
            echo json_encode($result);
        } catch (PDOException $e) {
            $result = [
                'code' => '3',
                'msg' => '出错',
            ];
            return json_encode($result);
            // die("Error!: " . $e->getMessage() . "<br/>");
        }
    }

}